# src/db/collection_db.py
from src.db.connection import get_db_connection, close_db_connection
from src.utils.logger import get_logger

logger = get_logger(__name__)

def add_collection(user_id: int, artwork_id: int) -> bool:
    """添加艺术品到收藏夹（避免重复收藏）"""
    conn = get_db_connection()
    if not conn:
        return False
    
    try:
        with conn.cursor() as cursor:
            # 1. 检查是否已收藏
            cursor.execute("""
                SELECT * FROM collection 
                WHERE user_id = %s AND artwork_id = %s
            """, (user_id, artwork_id))
            if cursor.fetchone():
                logger.warning(f"艺术品已收藏（user_id: {user_id}, artwork_id: {artwork_id}）")
                return False
            
            # 2. 插入收藏记录
            sql = """
            INSERT INTO collection 
            (user_id, artwork_id, create_time)
            VALUES (%s, %s, CURRENT_TIMESTAMP)
            """
            cursor.execute(sql, (user_id, artwork_id))
            conn.commit()
            logger.info(f"收藏艺术品成功（user_id: {user_id}, artwork_id: {artwork_id}）")
            return True
    except Exception as e:
        conn.rollback()
        logger.error(f"收藏艺术品失败（user_id: {user_id}, artwork_id: {artwork_id}）：{str(e)}")
        return False
    finally:
        close_db_connection(conn)

def remove_collection(user_id: int, artwork_id: int) -> bool:
    """从收藏夹移除艺术品"""
    conn = get_db_connection()
    if not conn:
        return False
    
    try:
        with conn.cursor() as cursor:
            # 1. 检查是否已收藏
            cursor.execute("""
                SELECT * FROM collection 
                WHERE user_id = %s AND artwork_id = %s
            """, (user_id, artwork_id))
            if not cursor.fetchone():
                logger.warning(f"艺术品未收藏（user_id: {user_id}, artwork_id: {artwork_id}）")
                return False
            
            # 2. 删除收藏记录
            sql = """
            DELETE FROM collection 
            WHERE user_id = %s AND artwork_id = %s
            """
            cursor.execute(sql, (user_id, artwork_id))
            conn.commit()
            logger.info(f"取消收藏成功（user_id: {user_id}, artwork_id: {artwork_id}）")
            return True
    except Exception as e:
        conn.rollback()
        logger.error(f"取消收藏失败（user_id: {user_id}, artwork_id: {artwork_id}）：{str(e)}")
        return False
    finally:
        close_db_connection(conn)

def get_user_collections(user_id: int) -> list:
    """获取用户的所有收藏（关联艺术品详情）"""
    conn = get_db_connection()
    if not conn:
        return []
    
    try:
        with conn.cursor() as cursor:
            # 关联查询收藏的艺术品详情
            sql = """
            SELECT c.*, a.title, a.description, a.price, a.creator, a.category, a.era
            FROM collection c
            LEFT JOIN artwork a ON c.artwork_id = a.artwork_id
            WHERE c.user_id = %s
            ORDER BY c.create_time DESC
            """
            cursor.execute(sql, (user_id,))
            collections = cursor.fetchall()
            logger.info(f"获取用户收藏列表成功（user_id: {user_id}, 数量：{len(collections)}）")
            return collections
    except Exception as e:
        logger.error(f"获取用户收藏列表失败（user_id: {user_id}）：{str(e)}")
        return []
    finally:
        close_db_connection(conn)